package com.dnjn.happiness.emo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.dnjn.happiness.common.core.utils.DateUtils;
import com.dnjn.happiness.common.core.utils.StringUtils;
import com.dnjn.happiness.common.core.web.domain.AjaxResult;
import com.dnjn.happiness.common.security.utils.SecurityUtils;
import com.dnjn.happiness.emo.api.domain.EmoAvgScoreWeekly;
import com.dnjn.happiness.emo.api.domain.WeeklyRankingBean;
import com.dnjn.happiness.emo.domain.vo.ScoreSpreadVO;
import com.dnjn.happiness.emo.domain.vo.WeeklyComparisonVO;
import com.dnjn.happiness.emo.mapper.EmoAvgScoreWeeklyMapper;
import com.dnjn.happiness.emo.service.IEmoAvgScoreWeeklyService;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * 快乐值周均Service业务层处理
 *
 * @author happiness
 * @date 2024-02-27
 */
@Service
public class EmoAvgScoreWeeklyServiceImpl extends ServiceImpl<EmoAvgScoreWeeklyMapper, EmoAvgScoreWeekly> implements IEmoAvgScoreWeeklyService {
    private static final Logger log = LoggerFactory.getLogger(EmoAvgScoreWeeklyServiceImpl.class);

    @Override
    public List<EmoAvgScoreWeekly> getLargeFluctuate(String weekOfYear, Long deptId) {
        return baseMapper.getLargeFluctuate(weekOfYear, deptId);
    }

    /**
     * 导出快乐值周报表 均列表
     */
    @Override
    public void exportExcel(HttpServletResponse response, Long deptId) {
        //查询当前周
        int week = DateUtils.getWeek();
        //当前年数
        int year = DateUtils.getYear();
        String weekOfYear = String.format("%04d%02d", year, week);
        SXSSFWorkbook workbook = new SXSSFWorkbook(500);
        CellStyle style = createCellStyle(workbook);
        exportExcel(workbook, style, week, year, weekOfYear, deptId);
        try {
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(workbook);
        }
    }

    private void exportExcel(SXSSFWorkbook workbook, CellStyle style, int week, int year, String weekOfYear, Long deptId) {
//        List<EmoAvgScoreWeekly> agoList = lambdaQuery()
//                .eq(EmoAvgScoreWeekly::getWeekOfYear, weekOfYear).orderByDesc(EmoAvgScoreWeekly::getScoreAvg).last("limit 10").list();
        List<EmoAvgScoreWeekly> agoList = baseMapper.getAgoList(weekOfYear, deptId);
//        List<EmoAvgScoreWeekly> afterList = lambdaQuery()
//                .eq(EmoAvgScoreWeekly::getWeekOfYear, weekOfYear).orderByAsc(EmoAvgScoreWeekly::getScoreAvg).last("limit 10").list();
        List<EmoAvgScoreWeekly> afterList = baseMapper.getAfterList(weekOfYear, deptId);
        List<EmoAvgScoreWeekly> waveList = getLargeFluctuate(weekOfYear, deptId);
        SXSSFSheet sheet = workbook.createSheet("第" + week + "周");
        createTitle(style, sheet, year + "年第" + week + "周", 0, 0, 0, 0, 0, 18);

        mergeCell(sheet, 1, 1, 0, 5);
        mergeCell(sheet, 1, 1, 6, 11);
        mergeCell(sheet, 1, 1, 12, 17);
        SXSSFRow rowOne = sheet.createRow(1);
        setCellValueByRow(style, rowOne, 0, "前10名");
        setCellValueByRow(style, rowOne, 6, "后10名");
        setCellValueByRow(style, rowOne, 12, "波动较大10名");
        SXSSFRow secondRow = sheet.createRow(2);
        createSecondRow(style, sheet, secondRow);
        cellDataFilling(agoList, afterList, waveList, sheet);
    }

    private void cellDataFilling(List<EmoAvgScoreWeekly> agoList, List<EmoAvgScoreWeekly> afterList, List<EmoAvgScoreWeekly> waveList, SXSSFSheet sheet) {
        int index = 0;
        for (int i = 3; i <= 12; i++) {
            SXSSFRow row = sheet.createRow(i);
            index = 0;
            for (int j = 0; j <= 2; j++) {
                SXSSFCell cell0 = row.createCell(index);
                index++;
                SXSSFCell cell1 = row.createCell(index);
                index++;
                SXSSFCell cell2 = row.createCell(index);
                index++;
                SXSSFCell cell3 = row.createCell(index);
                index++;
                SXSSFCell cell4 = row.createCell(index);
                index++;
                SXSSFCell cell5 = row.createCell(index);
                index++;
                EmoAvgScoreWeekly weekly = null;
                if (j == 0) {
                    weekly = CollectionUtils.isEmpty(agoList) ? new EmoAvgScoreWeekly() : agoList.get(i - 3);
                } else if (j == 1) {
                    weekly = CollectionUtils.isEmpty(afterList) ? new EmoAvgScoreWeekly() : afterList.get(i - 3);
                } else {
                    weekly = CollectionUtils.isEmpty(waveList) ? new EmoAvgScoreWeekly() : waveList.get(i - 3);
                }
                cell0.setCellValue((i - 2) + "");
                cell1.setCellValue(weekly.getEmpno());
                cell2.setCellValue(weekly.getNickName());
                cell3.setCellValue(StringUtils.isNotNull(weekly.getScoreAvg()) ? weekly.getScoreAvg().toString() : "");
                cell4.setCellValue(StringUtils.isNotNull(weekly.getScoreChanged()) ? weekly.getScoreChanged().toString() : "");
                cell5.setCellValue(weekly.getRemark());
            }
        }
    }

    private void createSecondRow(CellStyle style, SXSSFSheet sheet, SXSSFRow secondRow) {
        List<String> list = new ArrayList<>();
        list.add("名次");
        list.add("工号");
        list.add("姓名");
        list.add("平均快乐值");
        list.add("较上周波动");
        list.add("备注");
        int flag = 0;
        for (int i = 0; i <= 2; i++) {
            for (int j = 0; j <= 5; j++) {
                SXSSFCell cell = secondRow.createCell(flag);
                cell.setCellStyle(style);
                cell.setCellValue(list.get(j));
                flag++;
                sheet.setColumnWidth(flag, 3000);
            }
        }
    }


    /**
     * 导出快乐值周对比报表
     */
    @Override
    public void exportWeeklyComparison(HttpServletResponse response) {
        //查询当前周
        int week = DateUtils.getWeek();
        //当前年数
        int year = DateUtils.getYear();
        String weekOfYear = String.format("%04d%02d", year, week);
        String lastWeekOfYear = String.format("%04d%02d", year, week - 1);
        Long deptId = SecurityUtils.getLoginUser().getDeptId();
        //较上周平均快乐值波动
        BigDecimal subWeeklyAvg = baseMapper.getWeeklyAvg(deptId);

//        subWeeklyAvg.setScale(2,BigDecimal.ROUND_HALF_UP);
        SXSSFWorkbook workbook = new SXSSFWorkbook(500);
        CellStyle style = createCellStyle(workbook);
        SXSSFSheet sheet = workbook.createSheet();
        List<String> intervalList = new ArrayList<>();
        intervalList.add("300-400");
        intervalList.add("400-500");
        intervalList.add("500-600");
        intervalList.add("600-700");
        intervalList.add("700-800");
        intervalList.add("800-900");
        //第一行
        createTitle(style, sheet, "周环比", 0, 0, 0, 0, 0, 4);
        //第二行
        createFirstRow(style, sheet);
        //第三行
        createThreeRow(style, sheet);
        //第四行
        createFourRow(weekOfYear, lastWeekOfYear, style, sheet, intervalList, deptId);
        //第十行
        createEndRow(subWeeklyAvg, style, sheet);

        try {
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(workbook);
        }

    }

    private void createEndRow(BigDecimal subWeeklyAvg, CellStyle style, SXSSFSheet sheet) {
        mergeCell(sheet, 9, 9, 0, 4);
        SXSSFRow row = sheet.createRow(9);
        SXSSFCell lastCell = row.createCell(0);
        lastCell.setCellStyle(style);
        DecimalFormat decimalFormat = new DecimalFormat("#.##");
        if (null != subWeeklyAvg) {
            lastCell.setCellValue(subWeeklyAvg.compareTo(BigDecimal.ZERO) > 0 ? "平均快乐值：+" + decimalFormat.format(subWeeklyAvg) : "平均快乐值：" + decimalFormat.format(subWeeklyAvg));
        } else {
            lastCell.setCellValue("平均快乐值：+0");
        }
    }

    private void createFourRow(String weekOfYear, String lastWeekOfYear, CellStyle style, SXSSFSheet sheet, List<String> intervalList, Long deptId) {
        for (int i = 3; i <= 8; i++) {
            SXSSFRow row = sheet.createRow(i);
            SXSSFCell cell0 = row.createCell(0);
            SXSSFCell cell1 = row.createCell(1);
            SXSSFCell cell2 = row.createCell(2);
            SXSSFCell cell3 = row.createCell(3);
            SXSSFCell cell4 = row.createCell(4);
            cell0.setCellStyle(style);
            cell1.setCellStyle(style);
            cell2.setCellStyle(style);
            cell3.setCellStyle(style);
            cell4.setCellStyle(style);
            cell0.setCellValue(intervalList.get(i - 3));
            cell2.setCellValue(intervalList.get(i - 3));
            int lastWeekRangePeople = baseMapper.getWeekRangePeople(lastWeekOfYear, Integer.parseInt(intervalList.get(i - 3).split("-")[0])
                    , Integer.parseInt(intervalList.get(i - 3).split("-")[1]), deptId);
            cell1.setCellValue(lastWeekRangePeople);
            int weekRangePeople = baseMapper.getWeekRangePeople(weekOfYear, Integer.parseInt(intervalList.get(i - 3).split("-")[0])
                    , Integer.parseInt(intervalList.get(i - 3).split("-")[1]), deptId);
            cell3.setCellValue(weekRangePeople);
            cell4.setCellValue(weekRangePeople - lastWeekRangePeople);
            sheet.setColumnWidth(i - 3, 3000);
        }
    }

    private void createFirstRow(CellStyle style, SXSSFSheet sheet) {
        mergeCell(sheet, 1, 1, 0, 1);
        mergeCell(sheet, 1, 1, 2, 3);
        SXSSFRow rowOne = sheet.createRow(1);
        setCellValueByRow(style, rowOne, 0, "上周");
        setCellValueByRow(style, rowOne, 2, "本周");
        SXSSFCell chainCell = rowOne.createCell(4);
        chainCell.setCellStyle(style);
        chainCell.setCellValue("环比");
    }

    private void mergeCell(SXSSFSheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cellAddresses);
    }

    private void createThreeRow(CellStyle style, SXSSFSheet sheet) {
        SXSSFRow rowThree = sheet.createRow(2);
        SXSSFCell cell1 = rowThree.createCell(0);
        cell1.setCellStyle(style);
        cell1.setCellValue("快乐值区间");
        SXSSFCell cell2 = rowThree.createCell(1);
        cell2.setCellStyle(style);
        cell2.setCellValue("人数");
        SXSSFCell cell3 = rowThree.createCell(2);
        cell3.setCellStyle(style);
        cell3.setCellValue("快乐值区间");
        SXSSFCell cell4 = rowThree.createCell(3);
        cell4.setCellStyle(style);
        cell4.setCellValue("人数");
        SXSSFCell cell5 = rowThree.createCell(4);
        cell5.setCellStyle(style);
        cell5.setCellValue("数量");
    }

    private CellStyle createCellStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }


    private void setCellValueByRow(CellStyle style, SXSSFRow rowOne, int cellIndex, String cellValue) {
        SXSSFCell cell0 = rowOne.createCell(cellIndex);
        cell0.setCellValue(cellValue);
        cell0.setCellStyle(style);
    }


    private void createTitle(CellStyle style, SXSSFSheet sheet, String cellValue, int startRowIndex, int startCellIndex, int firstRow, int lastRow, int firstCol, int lastCol) {
        SXSSFRow titleRow = sheet.createRow(startRowIndex);
        SXSSFCell headCell = titleRow.createCell(startCellIndex);
        headCell.setCellValue(cellValue);
        headCell.setCellStyle(style);
        CellRangeAddress cellAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cellAddress);
    }


    //查询统计分析周日历折线图
    @Override
    public List<WeeklyComparisonVO> getWeeklyComparison(Long deptId) {
        List<WeeklyComparisonVO> voList = baseMapper.getWeeklyComparison(deptId);
        for (int i = 0; i < voList.size(); i++) {
//            if (i != 0 && i != voList.size() -1){
            if (i != 0) {
                BigDecimal subtract = voList.get(i).getScoreAvg().subtract(voList.get(i - 1).getScoreAvg());
                voList.get(i).setScoreChanged(subtract.setScale(0, RoundingMode.HALF_UP));
            } else {
                voList.get(i).setScoreChanged(BigDecimal.ZERO);
            }
        }
        if (!CollectionUtils.isEmpty(voList)) {
            Integer week = voList.get(0).getWeek();
            if (week > 1) {
                for (int i = week - 1; i >= 1; i--) {
                    voList.add(0, new WeeklyComparisonVO().setWeek(i).setScoreAvg(BigDecimal.ZERO));
                }
            }
        }
        voList.forEach(el -> {
            if (Objects.nonNull(el.getScoreAvg())) {
                el.setScoreAvg(el.getScoreAvg().setScale(0, RoundingMode.HALF_UP));//?
            }
            if (Objects.nonNull(el.getScoreChanged())) {
                el.setScoreChanged(el.getScoreChanged().setScale(0, RoundingMode.HALF_UP));
            }

        });
        return voList;
    }

    //导出统计分析周日历
    @Override
    public void exportWeeklyCalendar(HttpServletResponse response, List<WeeklyComparisonVO> voList, Long deptId) {
        SXSSFWorkbook workbook = new SXSSFWorkbook(500);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        for (WeeklyComparisonVO vo : voList) {
            CellStyle style = createCellStyle(workbook);
            exportExcel(workbook, style, vo.getWeek(), vo.getYear(), vo.getWeekOfYear(), deptId);
        }
        try {
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(workbook);
        }


    }

    @Override
    public Integer selectCountByWeekOfYear(Integer startInterval, Integer endInterval, String weekOfYear, Long deptId) {
        return baseMapper.selectCountByWeekOfYear(startInterval, endInterval, weekOfYear, deptId);
    }

    @Override
    public AjaxResult getCompareWeekHappinessScore(Long deptId) {
        List<ScoreSpreadVO> list = new ArrayList<>();
        list.add(new ScoreSpreadVO().setStartInterval(300).setEndInterval(400));
        list.add(new ScoreSpreadVO().setStartInterval(400).setEndInterval(500));
        list.add(new ScoreSpreadVO().setStartInterval(500).setEndInterval(600));
        list.add(new ScoreSpreadVO().setStartInterval(600).setEndInterval(700));
        list.add(new ScoreSpreadVO().setStartInterval(700).setEndInterval(800));
        list.add(new ScoreSpreadVO().setStartInterval(800).setEndInterval(900));
        //上周list
        List<ScoreSpreadVO> lastList = new ArrayList<>();


        //查询当前周
        int week = DateUtils.getWeek();

        //当前年数
        int year = DateUtils.getYear();

        String weekOfYear = String.format("%04d%02d", year, week);
        String lastWeekOfYear = String.format("%04d%02d", year, week - 1);
        for (ScoreSpreadVO vo : list) {
            //查询本周区间内人数
            Integer count = baseMapper.selectCountByWeekOfYear(vo.getStartInterval(), vo.getEndInterval(), weekOfYear, deptId);
            //查询上周区间内人数
            Integer lastWeekCount = baseMapper.selectCountByWeekOfYear(vo.getStartInterval(), vo.getEndInterval(), lastWeekOfYear, deptId);
            vo.setTotalCount(count);
            vo.setTotalCountChange(count - lastWeekCount);
            lastList.add(new ScoreSpreadVO()
                    .setStartInterval(vo.getStartInterval())
                    .setEndInterval(vo.getEndInterval())
                    .setTotalCount(lastWeekCount));
        }
        //查询较上周 周均差
        BigDecimal weeklyMeanDeviation = baseMapper.getWeeklyAvg(deptId);

        return new AjaxResult().put("list", list).put("lastList", lastList).put("weeklyMeanDeviation", weeklyMeanDeviation);
    }

    @Override
    public List<WeeklyRankingBean> getTopTen(String weekOfYear, Long deptId) {
        return baseMapper.getTopTen(weekOfYear, deptId);
    }

    @Override
    public List<WeeklyRankingBean> getLastTen(String weekOfYear, Long deptId) {
        return baseMapper.getLastTen(weekOfYear, deptId);
    }

    @Override
    public List<EmoAvgScoreWeekly> getExportList(EmoAvgScoreWeekly emoAvgScoreWeekly) {
        return baseMapper.getExportList(emoAvgScoreWeekly);
    }

}
